set hive.vectorized.execution.enabled = false;

create table a1(KEHHAO string, START_DT string) partitioned by (END_DT string);
create table a2(KEHHAO string, START_DT string) partitioned by (END_DT string);

alter table a1 add partition(END_DT='20161020');
alter table a1 add partition(END_DT='20161021');

insert into table a1 partition(END_DT='20161020') values('2000721360','20161001');


SELECT T1.KEHHAO,COUNT(1) FROM (
SELECT KEHHAO FROM a1 T
WHERE T.KEHHAO = '2000721360' AND '20161018' BETWEEN T.START_DT AND T.END_DT-1
UNION ALL
SELECT KEHHAO FROM a2 T
WHERE T.KEHHAO = '2000721360' AND '20161018' BETWEEN T.START_DT AND T.END_DT-1
) T1
GROUP BY T1.KEHHAO
HAVING COUNT(1)>1;

SELECT T1.KEHHAO,COUNT(1) FROM (
SELECT KEHHAO FROM a1 T
WHERE T.KEHHAO = '2000721360' AND '20161018' BETWEEN T.START_DT AND T.END_DT-1
UNION ALL
SELECT KEHHAO FROM a1 T
WHERE T.KEHHAO = '2000721360' AND '20161018' BETWEEN T.START_DT AND T.END_DT-1
) T1
GROUP BY T1.KEHHAO
HAVING COUNT(1)>1;

DROP TABLE a1;
DROP TABLE a2;